<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class isp_print_excel extends MY_Controller {
	
	function __construct()
	{
		parent::__construct();

		$this->secure_session();
	}
	
	function komplain()
	{
		
		$x=0;

		$ls_sql	= " SELECT		A.kode_komplain,TO_CHAR(A.tgl_komplain,'DD/MM/YYYY') tgl_komplain,A.kode_customer,A.komplain,A.status,A.keterangan,B.NAMA,c.nama_status
					FROM		ISP_KOMPLAIN A
					LEFT JOIN 	ISP_CUSTOMER B ON A.KODE_CUSTOMER=B.KODE_CUSTOMER
					LEFT JOIN 	isp_komplain_status c ON a.status = c.kode_status
					WHERE		(UPPER(a.kode_komplain) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.tgl_komplain) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(b.kode_customer) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.komplain) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.status) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.keterangan) LIKE '%".strtoupper(post('keyword'))."%') 
									ORDER BY ".(post('sort')=='' ? 'a.kode_komplain' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		
		$ls_sql2	= "	SELECT 		COUNT(1)
						FROM		ISP_KOMPLAIN A
						LEFT JOIN 	ISP_CUSTOMER B ON A.KODE_CUSTOMER=B.KODE_CUSTOMER
						LEFT JOIN 	isp_komplain_status c ON a.status = c.kode_status
						WHERE		(UPPER(a.kode_komplain) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.tgl_komplain) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(b.kode_customer) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.komplain) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.status) LIKE '%".strtoupper(post('keyword'))."%'
									OR UPPER(a.keterangan) LIKE '%".strtoupper(post('keyword'))."%') 
									ORDER BY ".(post('sort')=='' ? 'a.kode_komplain' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));	
		
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Laporan Komplain_".$date);

		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO');
			$this->excel->getActiveSheet()->setCellValue('C8', 'KODE KOMPLAIN');
			$this->excel->getActiveSheet()->setCellValue('D8', 'TGL KOMPLAIN');
			$this->excel->getActiveSheet()->setCellValue('E8', 'KODE CLIENT');
			$this->excel->getActiveSheet()->setCellValue('F8', 'KOMPLAIN');
			$this->excel->getActiveSheet()->setCellValue('G8', 'STATUS');
			$this->excel->getActiveSheet()->setCellValue('H8', 'KETERANGAN');
			
			$this->excel->getActiveSheet()->getStyle('B8:H8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:H8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:H8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $no);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['kode_komplain']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['tgl_komplain']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['komplain']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['nama_status']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['keterangan']);
				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Komplain"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:H'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(22);

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('H1:H'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Komplain_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}


function customer()
	{
		
		$x=0;

		$ls_sql	= " SELECT	*
					FROM	ISP_CUSTOMER
					WHERE	STATUS = 'Berlangganan'
					ORDER BY ".(post('sort')=='' ? 'kode_customer' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order') );
					
		$ls_sql2	= "	SELECT COUNT(1)
						FROM	ISP_CUSTOMER
						WHERE	STATUS = 'Berlangganan'
					ORDER BY ".(post('sort')=='' ? 'kode_customer' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order') );
					
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Laporan Client");

		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO');
			$this->excel->getActiveSheet()->setCellValue('C8', 'KODE CLIENT');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NAMA');
			$this->excel->getActiveSheet()->setCellValue('E8', 'NO KTP');
			$this->excel->getActiveSheet()->setCellValue('F8', 'JABATAN');
			$this->excel->getActiveSheet()->setCellValue('G8', 'NAMA PERUSAHAAN');
			$this->excel->getActiveSheet()->setCellValue('H8', 'ALAMAT');
			$this->excel->getActiveSheet()->setCellValue('I8', 'KOTA');
			$this->excel->getActiveSheet()->setCellValue('J8', 'KODE POS');
			$this->excel->getActiveSheet()->setCellValue('K8', 'TELEPON');
			$this->excel->getActiveSheet()->setCellValue('L8', 'HANDPHONE');
			$this->excel->getActiveSheet()->setCellValue('M8', 'NO KONTAK LAIN');
			$this->excel->getActiveSheet()->setCellValue('N8', 'FAX');
			$this->excel->getActiveSheet()->setCellValue('O8', 'EMAIL');
			$this->excel->getActiveSheet()->setCellValue('P8', 'STATUS CLIENT');
			$this->excel->getActiveSheet()->setCellValue('Q8', 'TANGGAL SURVEY');
			$this->excel->getActiveSheet()->setCellValue('R8', 'TANGGAL INSTALASI');
			$this->excel->getActiveSheet()->setCellValue('S8', 'TANGGAL AKTIFASI');
			$this->excel->getActiveSheet()->setCellValue('T8', 'BERHENTI BERLANGGANAN');
			
			$this->excel->getActiveSheet()->getStyle('B8:T8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:T8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:T8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $no);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['nama']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['no_id']);
				$this->excel->getActiveSheet()->getStyle('E'.$x)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['jabatan']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['nama_perusahaan']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['alamat']);
				$this->excel->getActiveSheet()->setCellValue('i'.$x, $rows2['kota']);
				$this->excel->getActiveSheet()->setCellValue('J'.$x, $rows2['kode_pos']);
				$this->excel->getActiveSheet()->setCellValue('K'.$x, $rows2['no_telp']);
				$this->excel->getActiveSheet()->setCellValue('L'.$x, $rows2['no_hp']);
				$this->excel->getActiveSheet()->setCellValue('M'.$x, $rows2['no_hp2']);
				$this->excel->getActiveSheet()->setCellValue('N'.$x, $rows2['no_fax']);
				$this->excel->getActiveSheet()->setCellValue('O'.$x, $rows2['email']);
				$this->excel->getActiveSheet()->setCellValue('P'.$x, $rows2['status']);
				$this->excel->getActiveSheet()->setCellValue('Q'.$x, $rows2['tgl_survey']);
				$this->excel->getActiveSheet()->setCellValue('R'.$x, $rows2['tgl_instalasi']);
				$this->excel->getActiveSheet()->setCellValue('S'.$x, $rows2['tgl_aktifasi']);
				$this->excel->getActiveSheet()->setCellValue('T'.$x, $rows2['tgl_berakhir']);
				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Customer"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:T'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(50);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('O')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('P')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('Q')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('R')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('S')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('T')->setWidth(22);


		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('H1:H'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Client_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}


function inventori()
	{
		
		$x=0;

		$ls_sql	= " SELECT	*
					FROM	ISP_INVENTORI
					WHERE	(UPPER(kode_inventori) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(nama_inventori) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(banyak) LIKE '%".strtoupper(post('keyword'))."%')
					ORDER BY ".(post('sort')=='' ? 'kode_inventori' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
		$ls_sql2	= "	SELECT COUNT(1)
						FROM	ISP_INVENTORI
						WHERE	(UPPER(kode_inventori) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(nama_inventori) LIKE '%".strtoupper(post('keyword'))."%'
								OR UPPER(banyak) LIKE '%".strtoupper(post('keyword'))."%')
					ORDER BY ".(post('sort')=='' ? 'kode_inventori' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Laporan Inventori");

		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO');
			$this->excel->getActiveSheet()->setCellValue('C8', 'KODE INVENTORI');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NAMA_INVENTORI');
			$this->excel->getActiveSheet()->setCellValue('E8', 'BANYAK');

			$this->excel->getActiveSheet()->getStyle('B8:E8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:E8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:E8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $no);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['kode_inventori']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['nama_inventori']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['banyak']);
				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Inventori"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:E'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('E1:E'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Inventori_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}
	function customer2()
	{
		
		$x=0;

		$ls_sql	= " SELECT	*
					FROM	ISP_CUSTOMER
					WHERE	lower(status) = lower('Tidak Berlangganan')
					ORDER BY ".(post('sort')=='' ? 'kode_customer' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order') );
					
		$ls_sql2	= "	SELECT COUNT(1)
						FROM	ISP_CUSTOMER
					WHERE	lower(status) = lower('Tidak Berlangganan')
					ORDER BY ".(post('sort')=='' ? 'kode_customer' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order') );
					
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Client Tidak Berlangganan");

		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO');
			$this->excel->getActiveSheet()->setCellValue('C8', 'KODE CLIENT');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NAMA');
			$this->excel->getActiveSheet()->setCellValue('E8', 'NO KTP');
			$this->excel->getActiveSheet()->setCellValue('F8', 'JABATAN');
			$this->excel->getActiveSheet()->setCellValue('G8', 'NAMA PERUSAHAAN');
			$this->excel->getActiveSheet()->setCellValue('H8', 'ALAMAT');
			$this->excel->getActiveSheet()->setCellValue('I8', 'KOTA');
			$this->excel->getActiveSheet()->setCellValue('J8', 'KODE POS');
			$this->excel->getActiveSheet()->setCellValue('K8', 'TELEPON');
			$this->excel->getActiveSheet()->setCellValue('L8', 'HANDPHONE');
			$this->excel->getActiveSheet()->setCellValue('M8', 'FAX');
			$this->excel->getActiveSheet()->setCellValue('N8', 'EMAIL');
			$this->excel->getActiveSheet()->setCellValue('O8', 'STATUS CLIENT');
			
			$this->excel->getActiveSheet()->getStyle('B8:O8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:O8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:O8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $no);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['nama']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['no_id']);
				$this->excel->getActiveSheet()->getStyle('E'.$x)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['jabatan']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['nama_perusahaan']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['alamat']);
				$this->excel->getActiveSheet()->setCellValue('i'.$x, $rows2['kota']);
				$this->excel->getActiveSheet()->setCellValue('J'.$x, $rows2['kode_pos']);
				$this->excel->getActiveSheet()->setCellValue('K'.$x, $rows2['no_telp']);
				$this->excel->getActiveSheet()->setCellValue('L'.$x, $rows2['no_hp']);
				$this->excel->getActiveSheet()->setCellValue('M'.$x, $rows2['no_fax']);
				$this->excel->getActiveSheet()->setCellValue('N'.$x, $rows2['email']);
				$this->excel->getActiveSheet()->setCellValue('O'.$x, $rows2['status']);
				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Client Tidak Berlangganan"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:O'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('O')->setWidth(22);


		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('Z1:Z'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Client Tidak Berlangganan_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

	function invoice()
	{
		$tanggal=get('tanggal');
		$x=0;

		$ls_sql	= "	SELECT a.*,b.nama
					FROM ISP_CLIENT_INVOICE a
					LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer
					ORDER BY a.kode_kontrak 
					--WHERE a.periode_tagihan = '$tanggal' ";

		$ls_sql2	= "	SELECT COUNT(1)
						FROM ISP_CLIENT_INVOICE a
						LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer
						ORDER BY a.kode_kontrak
						--WHERE a.periode_tagihan = '$tanggal' ";

		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Invoice Berlangganan");

		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'KODE KONTRAK');
			$this->excel->getActiveSheet()->setCellValue('C8', 'KODE CLIENT');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NAMA CLIENT');
			$this->excel->getActiveSheet()->setCellValue('E8', 'NO INVOICE');
			$this->excel->getActiveSheet()->setCellValue('F8', 'TANGGAL INVOICE');
			$this->excel->getActiveSheet()->setCellValue('G8', 'STATUS');
			$this->excel->getActiveSheet()->setCellValue('H8', 'NAMA_JENIS_PAKET');
			$this->excel->getActiveSheet()->setCellValue('I8', 'BANDWIDTH');
			$this->excel->getActiveSheet()->setCellValue('J8', 'ABODEMEN');
			$this->excel->getActiveSheet()->setCellValue('K8', 'PPN');
			$this->excel->getActiveSheet()->setCellValue('L8', 'BIAYA PENDAFTARAN');
			$this->excel->getActiveSheet()->setCellValue('M8', 'TOTAL');
			$this->excel->getActiveSheet()->setCellValue('N8', 'TERBILANG');
			$this->excel->getActiveSheet()->setCellValue('O8', 'DUE DATE INVOICE');
			$this->excel->getActiveSheet()->setCellValue('P8', 'TANGGAL BAYAR');
			$this->excel->getActiveSheet()->setCellValue('Q8', 'PERIODE TAGIHAN');
			$this->excel->getActiveSheet()->setCellValue('R8', 'KETERANGAN');

			$this->excel->getActiveSheet()->getStyle('B8:R8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:R8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:R8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $rows2['kode_kontrak']);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['nama']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['no_invoice']);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['tanggal_invoice']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['status']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['nama_jenis_barang']);
				$this->excel->getActiveSheet()->setCellValue('I'.$x, $rows2['bandwidth']);
				$this->excel->getActiveSheet()->setCellValue('J'.$x, $rows2['abodemen']);
				$this->excel->getActiveSheet()->getStyle('J'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('K'.$x, $rows2['ppn']);
				$this->excel->getActiveSheet()->getStyle('K'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('L'.$x, $rows2['biaya_pendaftaran']);
				$this->excel->getActiveSheet()->getStyle('L'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('M'.$x, $rows2['total']);
				$this->excel->getActiveSheet()->getStyle('M'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('N'.$x, $rows2['terbilang']);
				$this->excel->getActiveSheet()->setCellValue('O'.$x, $rows2['due_date']);
				$this->excel->getActiveSheet()->setCellValue('p'.$x, $rows2['tanggal_bayar']);
				$this->excel->getActiveSheet()->setCellValue('Q'.$x, $rows2['periode_tagihan']);
				$this->excel->getActiveSheet()->setCellValue('R'.$x, $rows2['keterangan']);
				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Client Invoice Berlangganan"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:R'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(13);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(21);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(55);
		$this->excel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
		

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('V1:V'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Client Invoice Berlangganan_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

	function invoice_excel_satuan()
	{
		$no_invoice=get('no_invoice');
		$x=0;

		$ls_sql	= "	SELECT  a.id_invoice,a.input_ppn,a.kode_kontrak,a.kode_customer,a.no_invoice,
							a.abodemen,a.ppn,a.periode_tagihan,b.kota,b.kode_pos,
							TO_CHAR(a.tanggal_invoice,'DD/MM/YYYY') as tanggal_invoice,
							TO_CHAR(a.tanggal_bayar,'DD/MM/YYYY') as tanggal_bayar, 
							a.keterangan,a.terbilang,a.periode_tagihan,a.biaya_pendaftaran,
							TO_CHAR(a.due_date,'DD/MM/YYYY') as due_date,
							a.nama_jenis_barang,a.bandwidth,a.total,
							b.nama,b.nama_perusahaan,b.alamat,c.no_id
					FROM ISP_CLIENT_INVOICE a
					LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer
					JOIN ISP_CLIENT_KONTRAK c ON a.kode_kontrak = c.kode_kontrak
                    WHERE a.NO_INVOICE='$no_invoice'";

		$ls_sql2	= "	SELECT COUNT(1)
						FROM ISP_CLIENT_INVOICE a
						LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer
                    	WHERE a.NO_INVOICE='$no_invoice'";

		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);
		
		



		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Invoice Berlangganan");

		$no = 1;

			
		if ($data_p) {

			$rows = $data_p['rows'];
			$rows2 = $rows[0];

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$styleArrayTerbilang = array(
				'font'  => array(
					'bold'  => true,
					'italic' => true,
					'size'  => 10,
					'name'  => 'Book Antiqua'
					)
			);

			$styleArrayDefault = array(
				'font'  => array(
					'size'  => 10,
					'name'  => 'Book Antiqua',
					'bold'  => true
					)
			);

			$styleArrayItalic = array(
				'font'  => array(
					'size'  => 10,
					'name'  => 'Book Antiqua',
					'italic'  => true,
					'bold'  => true
					)
			);

			$styleArrayInvoice = array(
				'font'  => array(
					'bold'  => true,
					'size'  => 18,
					'name'  => 'Book Antiqua'
					)
			);


		    $tgl_old = str_replace("/", "", $rows2['tanggal_invoice']);

		    $tahun_new = substr($tgl_old, 4, 4);
		    $bulan_new = substr($tgl_old, 2, 2);
		    $hari_new   = substr($tgl_old, 0, 2);
		   
		    if($bulan_new<='09'){
		    	$y = substr($bulan_new, 1, 1);

		    	$hasil = "";
		    	$iromawi =
		    	array("","Januari","Februari","Maret","April","Mei","Juni","Juli","Agustus","September");
		    	if(array_key_exists($y,$iromawi)){
		    		$hasil = $iromawi[$y];
		    	}
		    
		     $tgl_new = $hari_new." - ".$hasil." - ".$tahun_new." ";

		    } else {

		    	$hasil = "";
		    	$iromawi =
		    	array("","","","","","","","","","","Oktober","November","Desember");
		    	if(array_key_exists($bulan_new,$iromawi)){
		    		$hasil = $iromawi[$bulan_new];
		    	}
		    	
		    $tgl_new = $hari_new." - ".$hasil." - ".$tahun_new." ";

		    }


		    $tgl_old1 = str_replace("/", "", $rows2['due_date']);

		    $tahun_new1 = substr($tgl_old1, 4, 4);
		    $bulan_new1 = substr($tgl_old1, 2, 2);
		    $hari_new1   = substr($tgl_old1, 0, 2);

		    if($bulan_new1<='09'){
		    	$y1 = substr($bulan_new1, 1, 1);

		    	$hasil1 = "";
		    	$iromawi1 =
		    	array("","Januari","Februari","Maret","April","Mei","Juni","Juli","Agustus","September");
		    	if(array_key_exists($y1,$iromawi1)){
		    		$hasil1 = $iromawi[$y1];
		    	}
		    
		     $tgl_new1 = $hari_new1." - ".$hasil1." - ".$tahun_new1." ";
		     	
		    } else {

		    	$hasil1 = "";
		    	$iromawi1 =
		    	array("","","","","","","","","","","Oktober","November","Desember");
		    	if(array_key_exists($bulan_new1,$iromawi1)){
		    		$hasil1 = $iromawi1[$bulan_new1];
		    	}
		    	
		    $tgl_new1 = $hari_new1." - ".$hasil1." - ".$tahun_new1." ";

		    }

		    $periode_tagihan = str_replace("-", " ", $rows2['periode_tagihan']);

			//setting prorate-------------------------------------------------------------------------------------------------------------------------------//


			$this->excel->getActiveSheet()->setCellValue('A1', 'INVOICE');
			$this->excel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArrayInvoice);
			
			$this->excel->getActiveSheet()->setCellValue('A3', 	$rows2['no_invoice']);
			$this->excel->getActiveSheet()->setCellValue('A4', 'Nama Pelanggan');
			$this->excel->getActiveSheet()->setCellValue('A5',  $rows2['nama_perusahaan']);
			$this->excel->getActiveSheet()->setCellValue('A7',  $rows2['alamat']."  ".$rows2['kota']."  ".$rows2['kode_pos']);
			$this->excel->getActiveSheet()->mergeCells('A7:D8');
			$this->excel->getActiveSheet()->getStyle('A7:D8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
			$this->excel->getActiveSheet()->getStyle('A7:D8')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
			$this->excel->getActiveSheet()->getStyle('A7:D8')->getAlignment()->setWrapText(true);
			$this->excel->getActiveSheet()->setCellValue('A8', 'Cilegon');
			$this->excel->getActiveSheet()->getStyle('A3:K3')->getBorders()->getBottom()->setBorderStyle( PHPExcel_Style_Border::BORDER_THIN);

			$this->excel->getActiveSheet()->setCellValue('E5', 'CUSTCODE');
			$this->excel->getActiveSheet()->setCellValue('E6', 'Periode');
			$this->excel->getActiveSheet()->setCellValue('E7', 'Contact Person');
			$this->excel->getActiveSheet()->setCellValue('E8', 'No. FPB/Kontrak');
			$this->excel->getActiveSheet()->setCellValue('E9', 'Jatuh Tempo/Due Date');

			$this->excel->getActiveSheet()->setCellValue('G3', 'Cilegon, '.$tgl_new);

			$this->excel->getActiveSheet()->setCellValue('H5', ':');
			$this->excel->getActiveSheet()->setCellValue('H6', ':');
			$this->excel->getActiveSheet()->setCellValue('H7', ':');
			$this->excel->getActiveSheet()->setCellValue('H8', ':');
			$this->excel->getActiveSheet()->setCellValue('H9', ':');

			$this->excel->getActiveSheet()->setCellValue('I5', $rows2['kode_customer']);
			$this->excel->getActiveSheet()->setCellValue('I6', $periode_tagihan);
			$this->excel->getActiveSheet()->setCellValue('I7', $rows2['nama']);
			$this->excel->getActiveSheet()->setCellValue('I8', $rows2['kode_kontrak']);
			$this->excel->getActiveSheet()->setCellValue('I9', $tgl_new1);
			$this->excel->getActiveSheet()->getStyle('A9:K9')->getBorders()->getBottom()->setBorderStyle( PHPExcel_Style_Border::BORDER_THIN);

			$this->excel->getActiveSheet()->setCellValue('A10', 'Uraian   :');

			if ($rows2['input_ppn']==""){
				$ppn = 10;
			} else {
				$ppn = $rows2['input_ppn'];
			}

			$cek_data = $this->db->query("select * from isp_custom_invoice_detail where no_invoice = '".$rows2['no_invoice']."'");
			if($cek_data->num_rows()==0){
				$query2 = $this->db->query("SELECT TO_CHAR(tgl_aktifasi,'dd/mm/yyyy') as tgl_aktifasi FROM isp_client_kontrak WHERE no_id = '".$rows2['no_id']."'");
				$result2 = $query2->row_array(); 
				//var_dump($result2['tgl_aktifasi']);
				$sysdate_cal = date('d');

				$query = $this->db->query("SELECT LAST_DAY(tgl_aktifasi) as prorate from isp_client_kontrak WHERE kode_kontrak='".$rows2['kode_kontrak']."'");
				$result = $query->row_array();

				$tanggal_pembanding = explode("-", $result['prorate']);

				/*$bulan_periode = explode("-", $rows2['periode_tagihan']);
				$hasil = "";
		    	$iromawi =
		    	array("Januari"=>"01","Februari"=>"02","Maret"=>"03","April"=>"04","Mei"=>"05","Juni"=>"06","Juli"=>"07","Agustus"=>"08","September"=>"09","Oktober"=>"10","November"=>"11","Desember"=>"12");
		    	if(array_key_exists($bulan_periode[0],$iromawi)){
		    		$hasil = $iromawi[$bulan_periode[0]];
		    	}*/

				$query3 = $this->db->query("SELECT TO_CHAR(ADD_MONTHS(tanggal_invoice,-1),'dd/mm/yyyy') as tanggal_invoice FROM isp_client_invoice WHERE id_invoice = '".$rows2['id_invoice']."'");
			      if($query3->row_array()){
			        $getquery3 = $query3->row_array();
			        $triger = $getquery3['tanggal_invoice'];
			      }


			    $triger = explode("/", $triger);
				$tanggal_aktifasi = explode("/", $result2['tgl_aktifasi']);

				if($tanggal_aktifasi[0]!=1 && $tanggal_aktifasi[1]==$triger[1] && $tanggal_aktifasi[2]==$triger[2]){

						$tanggal_prorate = ($tanggal_pembanding[0]+1)-$tanggal_aktifasi[0];

						$this->excel->getActiveSheet()->setCellValue('A11', 'Biaya Pendaftaran ');
						$this->excel->getActiveSheet()->setCellValue('A12', 'Paket berlangganan Internet '.$rows2['nama_jenis_barang'].' '.$rows2['bandwidth']);
						$this->excel->getActiveSheet()->setCellValue('A13', '(Prorate '.$tanggal_prorate.' Hari)');
						$this->excel->getActiveSheet()->setCellValue('A14', 'PPN  '.$ppn.' %');
						$this->excel->getActiveSheet()->setCellValue('A15', 'TOTAL');

						$this->excel->getActiveSheet()->setCellValue('G11', ':');
						$this->excel->getActiveSheet()->setCellValue('G13', ':');
						$this->excel->getActiveSheet()->setCellValue('G14', ':');
						$this->excel->getActiveSheet()->setCellValue('G15', ':');

						$this->excel->getActiveSheet()->setCellValue('H11', 'Rp.');
						$this->excel->getActiveSheet()->setCellValue('H13', 'Rp.');
						$this->excel->getActiveSheet()->setCellValue('H14', 'Rp.');
						$this->excel->getActiveSheet()->setCellValue('H15', 'Rp.');

						$abodemen_cal = $rows2['abodemen'] / $tanggal_pembanding[0];
						$prorate_abodemen = $abodemen_cal * $tanggal_prorate;

						$prorate_ppn = ($prorate_abodemen) * 0.1;

						$prorate_total = $prorate_abodemen + $prorate_ppn + $rows2['biaya_pendaftaran'];

						$this->excel->getActiveSheet()->setCellValue('I11', $rows2['biaya_pendaftaran']);
						$this->excel->getActiveSheet()->setCellValue('I13', $prorate_abodemen);
						$this->excel->getActiveSheet()->setCellValue('I14', $prorate_ppn);
						$this->excel->getActiveSheet()->setCellValue('I15', $prorate_total);
						$this->excel->getActiveSheet()->getStyle('I11:I15')->getNumberFormat()->setFormatCode('#,##0');
						$this->excel->getActiveSheet()->getStyle('I12:I15')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
						$this->excel->getActiveSheet()->setCellValue('J14', '(+)');
						$this->excel->getActiveSheet()->getStyle('H14:J14')->getBorders()->getBottom()->setBorderStyle( PHPExcel_Style_Border::BORDER_THIN);
						$this->excel->getActiveSheet()->getStyle('A3:J18:')->applyFromArray($styleArrayDefault);
						
						$this->excel->getActiveSheet()->setCellValue('A18', 'Terbilang  :');
						$this->excel->getActiveSheet()->setCellValue('A19', '# '.$this->toTerbilang(intval($prorate_total)).' #');
						$this->excel->getActiveSheet()->getStyle('A19')->applyFromArray($styleArrayTerbilang);

						$this->excel->getActiveSheet()->setCellValue('A21', 'Pembayaran Via Rekening');
						$this->excel->getActiveSheet()->setCellValue('A23', 'BANK MANDIRI - Cilegon Anyer');
						$this->excel->getActiveSheet()->setCellValue('A24', 'No. Rek : 163-0000-25346-1');
						$this->excel->getActiveSheet()->setCellValue('A25', 'a/n : PRIMKOKAS Cyber-K');

						$this->excel->getActiveSheet()->setCellValue('A27', '*Jika mentransfer pembayaran mohon mencantumkan kode pelanggan ini : '.$rows2['kode_customer'].' pada berita acara');
						$this->excel->getActiveSheet()->setCellValue('A28', 'Hormat Kami, ');
						$this->excel->getActiveSheet()->setCellValue('A29', 'Cyber-K PRIMKOKAS');
						$this->excel->getActiveSheet()->setCellValue('A30', 'Information System Division');

						$this->excel->getActiveSheet()->setCellValue('A38', 'Maulana Rusli');
						$this->excel->getActiveSheet()->setCellValue('A39', 'Information System Div. Manager');
						$this->excel->getActiveSheet()->getStyle('A18:J39')->applyFromArray($styleArrayDefault);
						$this->excel->getActiveSheet()->setCellValue('A40', 'Tembusan :');
						$this->excel->getActiveSheet()->setCellValue('B41', '•Accounting Dept.');
						$this->excel->getActiveSheet()->setCellValue('B42', '•Finance Dept.');
						$this->excel->getActiveSheet()->setCellValue('B43', '•Arsip');
						$this->excel->getActiveSheet()->getStyle('A40:J43')->applyFromArray($styleArrayItalic);

					} else {
						
						$this->excel->getActiveSheet()->setCellValue('A12', 'Biaya Pendaftaran');
						$this->excel->getActiveSheet()->setCellValue('A13', 'Paket berlangganan Internet '.$rows2['nama_jenis_barang'].' '.$rows2['bandwidth']);
						$this->excel->getActiveSheet()->setCellValue('A14', 'PPN  '.$ppn.' %');
						$this->excel->getActiveSheet()->setCellValue('A15', 'TOTAL');

						$this->excel->getActiveSheet()->setCellValue('G12', ':');
						$this->excel->getActiveSheet()->setCellValue('G13', ':');
						$this->excel->getActiveSheet()->setCellValue('G14', ':');
						$this->excel->getActiveSheet()->setCellValue('G15', ':');

						$this->excel->getActiveSheet()->setCellValue('H12', 'Rp.');
						$this->excel->getActiveSheet()->setCellValue('H13', 'Rp.');
						$this->excel->getActiveSheet()->setCellValue('H14', 'Rp.');

						$this->excel->getActiveSheet()->setCellValue('I12', $rows2['biaya_pendaftaran']);
						$this->excel->getActiveSheet()->setCellValue('I13', $rows2['abodemen']);
						$this->excel->getActiveSheet()->setCellValue('I14', $rows2['ppn']);
						$this->excel->getActiveSheet()->setCellValue('I15', $rows2['total']);
						$this->excel->getActiveSheet()->getStyle('I12:I15')->getNumberFormat()->setFormatCode('#,##0');
						$this->excel->getActiveSheet()->getStyle('I12:I15')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
						$this->excel->getActiveSheet()->setCellValue('J15', '(+)');
						$this->excel->getActiveSheet()->getStyle('H14:J14')->getBorders()->getBottom()->setBorderStyle( PHPExcel_Style_Border::BORDER_THIN);
						$this->excel->getActiveSheet()->getStyle('A3:J18')->applyFromArray($styleArrayDefault);
						
						$this->excel->getActiveSheet()->setCellValue('A18', 'Terbilang  :');
						$this->excel->getActiveSheet()->setCellValue('A19', '# '.$rows2['terbilang'].' #');
						$this->excel->getActiveSheet()->getStyle('A19')->applyFromArray($styleArrayTerbilang);

						$this->excel->getActiveSheet()->setCellValue('A21', 'Pembayaran Via Rekening');
						$this->excel->getActiveSheet()->setCellValue('A23', 'BANK MANDIRI - Cilegon Anyer');
						$this->excel->getActiveSheet()->setCellValue('A24', 'No. Rek : 163-0000-25346-1');
						$this->excel->getActiveSheet()->setCellValue('A25', 'a/n : PRIMKOKAS Cyber-K');

						$this->excel->getActiveSheet()->setCellValue('A27', '*Jika mentransfer pembayaran mohon mencantumkan kode pelanggan ini : '.$rows2['kode_customer'].' pada berita acara');
						$this->excel->getActiveSheet()->setCellValue('A28', 'Hormat Kami, ');
						$this->excel->getActiveSheet()->setCellValue('A29', 'Cyber-K PRIMKOKAS');
						$this->excel->getActiveSheet()->setCellValue('A30', 'Information System Division');

						$this->excel->getActiveSheet()->setCellValue('A38', 'Maulana Rusli');
						$this->excel->getActiveSheet()->setCellValue('A39', 'Information System Div. Manager');
						$this->excel->getActiveSheet()->getStyle('A18:J39')->applyFromArray($styleArrayDefault);
						$this->excel->getActiveSheet()->setCellValue('A40', 'Tembusan :');
						$this->excel->getActiveSheet()->setCellValue('B41', '•Accounting Dept.');
						$this->excel->getActiveSheet()->setCellValue('B42', '•Finance Dept.');
						$this->excel->getActiveSheet()->setCellValue('B43', '•Arsip');
						$this->excel->getActiveSheet()->getStyle('A40:J43')->applyFromArray($styleArrayItalic);
					}
				 
				} else {

					$ls_sql3	= " SELECT		id_custom_detail,harga,uraian,kode_kontrak,no_invoice
						FROM		isp_custom_invoice_detail
						WHERE		no_invoice='$no_invoice' ";


					$ls_sql4	= "	SELECT		COUNT(1)
									FROM		isp_custom_invoice_detail
									WHERE		no_invoice='$no_invoice' ";
												

					$totald = $this->get_data($ls_sql4);

					$resultd = $this->db->query($ls_sql3);

					$data_p1 = array(	'rowsd'=> $resultd->result_array(), 
											'totald' => $totald);

					$x=11;
					for($ia=0;$ia<=$data_p1['totald']-1;$ia++){
						$x++;

						$rows3 = $data_p1['rowsd'];
						$rows4 = $rows3[$ia];
						
						$this->excel->getActiveSheet()->setCellValue('A'.$x, $rows4['uraian']);
						$this->excel->getActiveSheet()->setCellValue('I'.$x, $rows4['harga']);
						$this->excel->getActiveSheet()->setCellValue('G'.$x, ':');
						$this->excel->getActiveSheet()->setCellValue('H'.$x, 'Rp.');
						$this->excel->getActiveSheet()->getStyle('I'.$x)->getNumberFormat()->setFormatCode('#,##0');
						$this->excel->getActiveSheet()->getStyle('I'.$x)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					}						
						$y = $x +1;
						$ya = $y +1;
						$yb = $ya +3;
						$yc = $yb +1;
						$yd = $yc +2;
						$ye = $yd +2;
						$yf = $ye +1;
						$yg = $yf +1;
						$yh = $yg +2;
						$yi = $yh +1;
						$yj = $yi +1;
						$yk = $yj +1;
						$yl = $yk +8;
						$ym = $yl +1;
						$yn = $ym +1;
						$yo = $yn +1;
						$yp = $yo +1;
						$yq = $yp +1;


					$this->excel->getActiveSheet()->setCellValue('A'.$y, 'PPN 10% ');
					$this->excel->getActiveSheet()->setCellValue('A'.$ya, 'TOTAL');

					$this->excel->getActiveSheet()->setCellValue('G'.$y, ':');
					$this->excel->getActiveSheet()->setCellValue('G'.$ya, ':');
					
					$this->excel->getActiveSheet()->setCellValue('H'.$y, 'Rp.');
					$this->excel->getActiveSheet()->setCellValue('H'.$ya, 'Rp.');

					$query3 = $this->db->query("SELECT SUM(harga) as total from isp_custom_invoice_detail WHERE no_invoice = '".$rows2['no_invoice']."' ");
					$custom_total = $query3->row_array();

					$custom_ppn = $custom_total['total'] * 0.1;

					$grand_total = $custom_ppn + $custom_total['total'];
					
					$this->excel->getActiveSheet()->setCellValue('I'.$y, $custom_ppn);
					$this->excel->getActiveSheet()->setCellValue('I'.$ya, $grand_total);
					$this->excel->getActiveSheet()->getStyle('I'.$y.':I'.$ya)->getNumberFormat()->setFormatCode('#,##0');
					$this->excel->getActiveSheet()->getStyle('I'.$y.':I'.$ya)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
					$this->excel->getActiveSheet()->setCellValue('J'.$y, '(+)');
					$this->excel->getActiveSheet()->getStyle('H'.$y.':J'.$y)->getBorders()->getBottom()->setBorderStyle( PHPExcel_Style_Border::BORDER_THIN);
					$this->excel->getActiveSheet()->getStyle('A3:J'.$ya)->applyFromArray($styleArrayDefault);

					$this->excel->getActiveSheet()->setCellValue('A'.$yb, 'Terbilang  :');
					$this->excel->getActiveSheet()->setCellValue('A'.$yb, '# '.$this->toTerbilang($grand_total).' #');
					$this->excel->getActiveSheet()->getStyle('A'.$yb)->applyFromArray($styleArrayTerbilang);

					$this->excel->getActiveSheet()->setCellValue('A'.$yd, 'Pembayaran Via Rekening');
					$this->excel->getActiveSheet()->setCellValue('A'.$ye, 'BANK MANDIRI - Cilegon Anyer');
					$this->excel->getActiveSheet()->setCellValue('A'.$yf, 'No. Rek : 163-0000-25346-1');
					$this->excel->getActiveSheet()->setCellValue('A'.$yg, 'a/n : PRIMKOKAS Cyber-K');

					$this->excel->getActiveSheet()->setCellValue('A'.$yh, '*Jika mentransfer pembayaran mohon mencantumkan kode pelanggan ini : '.$rows2['kode_customer'].' pada berita acara');
					$this->excel->getActiveSheet()->setCellValue('A'.$yi, 'Hormat Kami, ');
					$this->excel->getActiveSheet()->setCellValue('A'.$yj, 'Cyber-K PRIMKOKAS');
					$this->excel->getActiveSheet()->setCellValue('A'.$yk, 'Information System Division');

					$this->excel->getActiveSheet()->setCellValue('A'.$yl, 'Maulana Rusli');
					$this->excel->getActiveSheet()->setCellValue('A'.$ym, 'Information System Div. Manager');
					$this->excel->getActiveSheet()->setCellValue('A'.$yn, 'Tembusan :');
					$this->excel->getActiveSheet()->setCellValue('B'.$yo, '•Accounting Dept.');
					$this->excel->getActiveSheet()->setCellValue('B'.$yp, '•Finance Dept.');
					$this->excel->getActiveSheet()->setCellValue('B'.$yq, '•Arsip');
					$this->excel->getActiveSheet()->getStyle('A'.$yd.':J'.$ym)->applyFromArray($styleArrayDefault);
					$this->excel->getActiveSheet()->getStyle('A'.$yo.':J'.$yq)->applyFromArray($styleArrayItalic);
				}
		}

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

/*		$this->excel->getActiveSheet()->getStyle('B8:R'.$x)->applyFromArray($styleArray);
		unset($styleArray);
*/
		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(2.29);
		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(8.86);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(0.58);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(19.29);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(1.29);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(19.29);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(1);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(2.71);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(21.29);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(2.49);
		

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('V1:V'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Client Invoice Berlangganan_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}



	function invoice_tak_berlangganan()
	{
		$tanggal=get('tanggal');
		$x=0;

		$ls_sql	= "	SELECT a.*,b.nama
					FROM ISP_CLIENT_INVOICE_TDK_LANGGAN a
					LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer 
					WHERE a.tanggal_invoice LIKE '%$tanggal %' ";

		$ls_sql2	= "	SELECT COUNT(1)
						FROM ISP_CLIENT_INVOICE_TDK_LANGGAN a
						LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer
						WHERE a.tanggal_invoice LIKE '%$tanggal %'  ";

		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Tidak Berlangganan");
 
		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO INVOICE');
			$this->excel->getActiveSheet()->setCellValue('C8', 'NAMA CLIENT');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NO INVOICE');
			$this->excel->getActiveSheet()->setCellValue('E8', 'TANGGAL INVOICE');
			$this->excel->getActiveSheet()->setCellValue('F8', 'STATUS');
			$this->excel->getActiveSheet()->setCellValue('G8', 'NAMA PRODUK');
			$this->excel->getActiveSheet()->setCellValue('H8', 'HARGA SATUAN');
			$this->excel->getActiveSheet()->setCellValue('I8', 'JUMLAH');
			$this->excel->getActiveSheet()->setCellValue('J8', 'PPN');
			$this->excel->getActiveSheet()->setCellValue('K8', 'TOTAL BAYAR');
			$this->excel->getActiveSheet()->setCellValue('L8', 'TERBILANG');
			$this->excel->getActiveSheet()->setCellValue('M8', 'TANGGAL BAYAR');
			$this->excel->getActiveSheet()->setCellValue('N8', 'KETERANGAN');

			$this->excel->getActiveSheet()->getStyle('B8:N8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:N8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:N8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['nama']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['no_invoice']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['tanggal_invoice']);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['status']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['nama_produk']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['harga_satuan']);
				$this->excel->getActiveSheet()->setCellValue('I'.$x, $rows2['jumlah']);
				$this->excel->getActiveSheet()->setCellValue('J'.$x, $rows2['ppn']);
				$this->excel->getActiveSheet()->setCellValue('K'.$x, $rows2['total_bayar']);
				$this->excel->getActiveSheet()->setCellValue('L'.$x, $rows2['terbilang']);
				$this->excel->getActiveSheet()->setCellValue('M'.$x, $rows2['tanggal_bayar']);
				$this->excel->getActiveSheet()->setCellValue('N'.$x, $rows2['keterangan']);
 				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Client Invoice Tidak Berlangganan"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:N'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(21);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
		$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(55);
		$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
		

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('V1:V'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Client Invoice Berlangganan_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

	function customer_trial()
	{
		$tanggal=get('tanggal');
		$x=0;

		$ls_sql	= " SELECT	*
					FROM	ISP_CUSTOMER
					WHERE	lower(status) = lower('Trial')
					ORDER BY ".(post('sort')=='' ? 'kode_customer' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order') );
					
		$ls_sql2	= "	SELECT COUNT(1)
						FROM	ISP_CUSTOMER
					WHERE	lower(status) = lower('Trial')
					ORDER BY ".(post('sort')=='' ? 'kode_customer' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order') );
					
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Client Trial");
 
		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'NO');
			$this->excel->getActiveSheet()->setCellValue('C8', 'KODE CLIENT');
			$this->excel->getActiveSheet()->setCellValue('D8', 'NAMA');
			$this->excel->getActiveSheet()->setCellValue('E8', 'NO KTP');
			$this->excel->getActiveSheet()->setCellValue('F8', 'JABATAN');
			$this->excel->getActiveSheet()->setCellValue('G8', 'NAMA PERUSAHAAN');
			$this->excel->getActiveSheet()->setCellValue('H8', 'ALAMAT');
			$this->excel->getActiveSheet()->setCellValue('I8', 'KOTA');
			$this->excel->getActiveSheet()->setCellValue('J8', 'KODE POS');
			$this->excel->getActiveSheet()->setCellValue('K8', 'TELEPON');
			$this->excel->getActiveSheet()->setCellValue('L8', 'HANDPHONE');
			$this->excel->getActiveSheet()->setCellValue('M8', 'FAX');
			$this->excel->getActiveSheet()->setCellValue('N8', 'EMAIL');
			$this->excel->getActiveSheet()->setCellValue('O8', 'STATUS CLIENT');
			$this->excel->getActiveSheet()->setCellValue('P8', 'TANGGAL SURVEY');
			$this->excel->getActiveSheet()->setCellValue('Q8', 'TANGGAL INSTALASI');
			$this->excel->getActiveSheet()->setCellValue('R8', 'LAMA TRIAL');
			$this->excel->getActiveSheet()->setCellValue('S8', 'TANGGAL AKTIFASI');
			$this->excel->getActiveSheet()->setCellValue('T8', 'BERHENTI TRIAL');

			$this->excel->getActiveSheet()->getStyle('B8:T8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:T8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:T8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $no);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['nama']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['no_id']);
				$this->excel->getActiveSheet()->getStyle('E'.$x)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['jabatan']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['nama_perusahaan']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['alamat']);
				$this->excel->getActiveSheet()->setCellValue('i'.$x, $rows2['kota']);
				$this->excel->getActiveSheet()->setCellValue('J'.$x, $rows2['kode_pos']);
				$this->excel->getActiveSheet()->setCellValue('K'.$x, $rows2['no_telp']);
				$this->excel->getActiveSheet()->setCellValue('L'.$x, $rows2['no_hp']);
				$this->excel->getActiveSheet()->setCellValue('M'.$x, $rows2['no_fax']);
				$this->excel->getActiveSheet()->setCellValue('N'.$x, $rows2['email']);
				$this->excel->getActiveSheet()->setCellValue('O'.$x, $rows2['status']);
				$this->excel->getActiveSheet()->setCellValue('P'.$x, $rows2['tgl_survey']);
				$this->excel->getActiveSheet()->setCellValue('Q'.$x, $rows2['tgl_instalasi']);
				$this->excel->getActiveSheet()->setCellValue('R'.$x, $rows2['tgl_trial']);
				$this->excel->getActiveSheet()->setCellValue('S'.$x, $rows2['tgl_aktifasi']);
				$this->excel->getActiveSheet()->setCellValue('T'.$x, $rows2['tgl_berhenti_trial']);
 				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Client Trial"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:T'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('N')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('O')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('P')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('Q')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('R')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('S')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('T')->setWidth(22);

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('V1:V'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Client Trial'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

	function invoice_trial()
	{
		$tanggal=get('tanggal');
		$x=0;

		$ls_sql	= "	SELECT a.*,b.nama
					FROM isp_customer_trial a
					LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer 
					WHERE a.tanggal_invoice LIKE '%$tanggal %'";

		$ls_sql2	= "	SELECT COUNT(1)
						FROM isp_customer_trial a
						LEFT JOIN ISP_CUSTOMER b ON a.kode_customer = b.kode_customer
						WHERE a.tanggal_invoice LIKE '%$tanggal %' ";	

		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Invoice Client Trial");
 
		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'KODE CLIENT');
			$this->excel->getActiveSheet()->setCellValue('C8', 'NAMA CLIENT');
			$this->excel->getActiveSheet()->setCellValue('D8', 'STATUS CLIENT');
			$this->excel->getActiveSheet()->setCellValue('E8', 'NO TRIAL');
			$this->excel->getActiveSheet()->setCellValue('F8', 'LAMA TRIAL');
			$this->excel->getActiveSheet()->setCellValue('G8', 'TANGGAL INVOICE');
			$this->excel->getActiveSheet()->setCellValue('H8', 'STATUS');
			$this->excel->getActiveSheet()->setCellValue('I8', 'BIAYA PENDAFTARAN');
			$this->excel->getActiveSheet()->setCellValue('J8', 'TOTAL BAYAR');
			$this->excel->getActiveSheet()->setCellValue('K8', 'TERBILANG');
			$this->excel->getActiveSheet()->setCellValue('L8', 'TANGGAL BAYAR');
			$this->excel->getActiveSheet()->setCellValue('M8', 'KETERANGAN');

			$this->excel->getActiveSheet()->getStyle('B8:M8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:M8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:M8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $rows2['kode_customer']);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['nama']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['status_customer']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['no_trial']);
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['lama_trial']);
				$this->excel->getActiveSheet()->setCellValue('G'.$x, $rows2['tanggal_invoice']);
				$this->excel->getActiveSheet()->setCellValue('H'.$x, $rows2['status']);
				$this->excel->getActiveSheet()->setCellValue('I'.$x, $rows2['biaya_pendaftaran']);
				$this->excel->getActiveSheet()->getStyle('I'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('J'.$x, $rows2['total_bayar']);
				$this->excel->getActiveSheet()->getStyle('J'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('K'.$x, $rows2['terbilang']);
				$this->excel->getActiveSheet()->setCellValue('L'.$x, $rows2['tanggal_bayar']);
				$this->excel->getActiveSheet()->setCellValue('M'.$x, $rows2['keterangan']);
 				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Invoice Client  Trial"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:M'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('H')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('I')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('J')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('K')->setWidth(55);
		$this->excel->getActiveSheet()->getColumnDimension('L')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('M')->setWidth(22);

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('V1:V'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Invoice Client Trial'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

	function produk_all()
	{
		$x=0;

		$ls_sql	= "	SELECT	*
					FROM	ISP_PRODUK
					WHERE	(UPPER(kode_jenis_barang) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(nama_jenis_barang) LIKE '%".strtoupper(post('keyword'))."%')
					ORDER BY ".(post('sort')=='' ? 'id_produk' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));

		$ls_sql2	= "	SELECT COUNT(1)
						FROM	ISP_PRODUK
						WHERE	(UPPER(kode_jenis_barang) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(nama_jenis_barang) LIKE '%".strtoupper(post('keyword'))."%')
						ORDER BY ".(post('sort')=='' ? 'id_produk' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));

		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('Y-m-d');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Tidak Berlangganan");
 
		$no = 1;

			
		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$this->excel->getActiveSheet()->setCellValue('B8', 'KODE JENIS BARANG');
			$this->excel->getActiveSheet()->setCellValue('C8', 'NAMA JENIS BARANG');
			$this->excel->getActiveSheet()->setCellValue('D8', 'BANDWIDTH');
			$this->excel->getActiveSheet()->setCellValue('E8', 'HARGA');
			$this->excel->getActiveSheet()->setCellValue('F8', 'KETERANGAN');

			$this->excel->getActiveSheet()->getStyle('B8:F8')->getFont()->setBold(true);
			$this->excel->getActiveSheet()->getStyle('B8:F8')->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID,'startcolor' => array('rgb' => 'EEEE11')));
			$this->excel->getActiveSheet()->getStyle('B8:F8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			$x = 8;

			for($i=0;$i<=$data_p['total']-1;$i++){
				$x++;

				$rows = $data_p['rows'];
				$rows2 = $rows[$i];

				$this->excel->getActiveSheet()->setCellValue('B'.$x, $rows2['kode_jenis_barang']);
				$this->excel->getActiveSheet()->setCellValue('C'.$x, $rows2['nama_jenis_barang']);
				$this->excel->getActiveSheet()->setCellValue('D'.$x, $rows2['bandwidth']);
				$this->excel->getActiveSheet()->setCellValue('E'.$x, $rows2['harga']);
				$this->excel->getActiveSheet()->getStyle('E'.$x)->getNumberFormat()->setFormatCode('Rp #,##0');
				$this->excel->getActiveSheet()->setCellValue('F'.$x, $rows2['keterangan']);
 				$no++;
			}
		}

		$this->excel->getActiveSheet()->setCellValue('B4', strtoupper("Laporan Data Produk"));
		$this->excel->getActiveSheet()->setCellValue('B5', '('.$date.')');			

		$styleArray = array(
			'borders' => array(
			    'allborders' => array(
			    'style' => PHPExcel_Style_Border::BORDER_THIN
			    )
			),
			'font'  => array(
			    'size'  => 9,
			    'name'  => 'verdana'
			)
		);

		$this->excel->getActiveSheet()->getStyle('B8:F'.$x)->applyFromArray($styleArray);
		unset($styleArray);

		$styleArray1 = array(
		    'font'  => array(
		    'bold'  => true,
		    'uppercase' => true,
		    'size'  => 11,
		    'name'  => 'Calibri'
		    )
		);

		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(22);
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(21);
		

		//$this->excel->getActiveSheet()->getStyle('D1:H'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('V1:V'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Laporan Data Produk_'.$date.'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}

	function penjualan_produk_satuan()
	{
		$id_pembelian=get('id_pembelian');
		$x=0;

		$ls_sql	= "	 SELECT a.id_pembelian,
							a.jumlah,
							a.total_bayar,
							b.kode_customer,
							b.nama,
							b.nama_perusahaan,
							c.kode_inventori,
							c.nama_inventori,
							c.banyak ,
							c.harga 
					FROM	isp_pembelian_produk a 
					LEFT JOIN isp_customer b ON a.kode_customer = b.kode_customer
					LEFT JOIN isp_inventori c ON a.kode_produk = c.kode_inventori
					WHERE a.id_pembelian='$id_pembelian'";	
	
		$ls_sql2	= "	SELECT 	COUNT(1)
						FROM	isp_pembelian_produk a 
						LEFT JOIN isp_customer b ON a.kode_customer = b.kode_customer
						LEFT JOIN isp_inventori c ON a.kode_produk = c.kode_inventori
						WHERE a.id_pembelian='$id_pembelian'";	
		
		$total = $this->get_data($ls_sql2);

		$result = $this->db->query($ls_sql);

		$data_p = array(	'rows'=> $result->result_array(), 
								'total' => $total);

		$date = date('d M Y');

		//load our new PHPExcel library
		$this->load->library('excel');
		//activate worksheet number 1
		$this->excel->setActiveSheetIndex(0);
		//name the worksheet
		$this->excel->getActiveSheet()->setTitle("Data Penjualan Produk");

		$no = 1;

		if ($data_p) {

			$styleArrayBorder = array(
				'borders' => array(
					'allborders' => array(
					'style' => PHPExcel_Style_Border::BORDER_THIN
					)
				)
			);

			$styleArrayUnderline = array(
			 'font' => array(
			 'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE,
			 'name' => 'Calibri',
			 'size' => 11,
			 'bold' => true
			 )
			);

			$styleArrayDefault = array(
			 'font' => array(
			 'name' => 'Calibri',
			 'size' => 11,
			 )
			);

			$styleArrayItalic = array(
			 'font' => array(
			 'name' => 'Calibri',
			 'size' => 11,
			 'italic' => true
			 )
			);

			$styleArrayFont = array(
			 'font' => array(
			 'name' => 'Calibri',
			 'size' => 9,
			 'color' => array('rgb' => 'FF4719'),
			 )
			);

			$styleArrayFont1 = array(
			 'font' => array(
			 'name' => 'Calibri',
			 'size' => 9,
			 'color' => array('rgb' => '1919FF'),
			 )
			);

			$rows = $data_p['rows'];
			$rows2 = $rows[0];

			/*foto logo*/
			$objDrawing = new PHPExcel_Worksheet_Drawing();
			$objDrawing->setName('Logo');
			$objDrawing->setDescription('Logo');
			$objDrawing->setPath(APPPATH.'file/media/logo_isp.jpg');
			$objDrawing->setCoordinates('A1');
			//$objDrawing->setHeight(120);
			//$objDrawing->setWidth(120);
			$objDrawing->setWorksheet($this->excel->getActiveSheet());
			$this->excel->getActiveSheet()->getStyle('A4:H4')->getBorders()->getBottom()->setBorderStyle( PHPExcel_Style_Border::BORDER_THIN);

			$this->excel->getActiveSheet()->setCellValue('A6', strtoupper('DATA PEMBELIAN PRODUK'));
			$this->excel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true)->setSize(18);
			/*$this->excel->getActiveSheet()->getStyle('A6')->getFont()->setSize(16);*/
			$this->excel->getActiveSheet()->setCellValue('A7', 'Kode Client ');
			$this->excel->getActiveSheet()->setCellValue('B7', ' : '.$rows2['kode_customer']);
			$this->excel->getActiveSheet()->setCellValue('A8', 'Nama Client ');
			$this->excel->getActiveSheet()->setCellValue('B8', ' : '.$rows2['nama']);
			$this->excel->getActiveSheet()->setCellValue('A9', 'Nama Perusahaan ');
			$this->excel->getActiveSheet()->setCellValue('B9', ' : '.$rows2['nama_perusahaan']);
			$this->excel->getActiveSheet()->setCellValue('A10', 'Nama Produk ');
			$this->excel->getActiveSheet()->setCellValue('B10', ' : '.$rows2['nama_inventori']);
			$this->excel->getActiveSheet()->setCellValue('A11', 'Harga Produk ');
			$this->excel->getActiveSheet()->setCellValue('B11', ' : '.$rows2['harga']);
			$this->excel->getActiveSheet()->setCellValue('A12', 'Jumlah Penjualan ');
			$this->excel->getActiveSheet()->setCellValue('B12', ' : '.$rows2['banyak']);
			$this->excel->getActiveSheet()->setCellValue('A13', 'Total Pembayaran ');
			$this->excel->getActiveSheet()->setCellValue('B13', ' : '.$rows2['total_bayar']);

			$this->excel->getActiveSheet()->setCellValue('A15', 'Pembayaran Via Rekening ');

			$this->excel->getActiveSheet()->setCellValue('A17', 'BANK MANDIRI - Cilegon Anyer ');
			$this->excel->getActiveSheet()->setCellValue('A18', 'No. Rek : 163-0000-25346-1 ');
			$this->excel->getActiveSheet()->setCellValue('A19', 'a/n : PRIMKOKAS Cyber-K');

			$this->excel->getActiveSheet()->setCellValue('A21', '*Jika mentransfer pembayaran mohon mencantumkan kode pelanggan ini : '.$rows2['kode_customer'].' pada berita acara ');
			$this->excel->getActiveSheet()->setCellValue('A22', 'Hormat Kami, ');
			$this->excel->getActiveSheet()->setCellValue('A23', 'Cyber-K PRIMKOKAS ');
			$this->excel->getActiveSheet()->setCellValue('A24', 'Information System Division ');

			$this->excel->getActiveSheet()->setCellValue('A27', 'Cilegon, 30-Nov-2014 ');
			$this->excel->getActiveSheet()->setCellValue('A28', 'Maulana Rusli ');

			/*foto ttd*/
			$objDrawing = new PHPExcel_Worksheet_Drawing();
			$objDrawing->setName('Logo');
			$objDrawing->setDescription('Logo');
			$objDrawing->setPath(APPPATH.'file/media/ttd_isp.jpg');
			$objDrawing->setCoordinates('A29');
			//$objDrawing->setHeight(120);
			//$objDrawing->setWidth(120);
			$objDrawing->setWorksheet($this->excel->getActiveSheet());

			$this->excel->getActiveSheet()->setCellValue('A34', 'Information System Div. Manager ');
			$this->excel->getActiveSheet()->setCellValue('A35', 'Tembusan : ');
			$this->excel->getActiveSheet()->setCellValue('A36', '- Accounting Dept. ');
			$this->excel->getActiveSheet()->setCellValue('A37', '- Finance Dept. ');
			$this->excel->getActiveSheet()->setCellValue('A38', '- Arsip ');

			/*$this->excel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArrayUnderline);
			$this->excel->getActiveSheet()->getStyle('A1:A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);	
			$this->excel->getActiveSheet()->getStyle('A1:A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
*/
			// $this->excel->getActiveSheet()->getStyle('E45:I45')->applyFromArray($styleArrayUnderline);
			// $this->excel->getActiveSheet()->getStyle('E39:I46')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		}

		$this->excel->getActiveSheet()->getColumnDimension('A')->setWidth(18);
		$this->excel->getActiveSheet()->getColumnDimension('B')->setWidth(12.14);
		$this->excel->getActiveSheet()->getColumnDimension('C')->setWidth(6.14);
		$this->excel->getActiveSheet()->getColumnDimension('D')->setWidth(15.41);
		/*$this->excel->getActiveSheet()->getRowDimension('17')->setRowHeight(80);*/
		$this->excel->getActiveSheet()->getColumnDimension('E')->setWidth(8.43);
		$this->excel->getActiveSheet()->getColumnDimension('F')->setWidth(8.43);
		$this->excel->getActiveSheet()->getColumnDimension('G')->setWidth(10.86);

/*
		$this->excel->getActiveSheet()->getStyle('D1:C'.$this->excel->getActiveSheet()->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00'); 

		$this->excel->getActiveSheet()->getStyle('C1:C'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);
    	$this->excel->getActiveSheet()->getStyle('G1:G'.$this->excel->getActiveSheet()->getHighestRow())
    	->getAlignment()->setWrapText(true);*/

		//set aligment to center for that merged cell (A1 to D1)
		// $this->excel->getActiveSheet()->getStyle('B1:B2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//$this->excel->getActiveSheet()->getStyle('B4:B5')->getFont()->setBold(true);

		$filename= 'Data Penyelenggara_'.$rows2['kode_customer'].'.xls'; //save our workbook as this file name
		header('Content-Type: application/vnd.ms-excel'); //mime type
		header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
		header('Cache-Control: max-age=0'); //no cache
			             
		//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
		//if you want to save it as .XLSX Excel 2007 format
		$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
		//force user to download the Excel file without writing it to server's HD
		$objWriter->save('php://output');
	}


	function terbilang_get_valid($str,$from,$to,$min=1,$max=9){
			$val=false;
			$from=($from<0)?0:$from;
			for ($i=$from;$i<$to;$i++){
				if (((int) $str{$i}>=$min)&&((int) $str{$i}<=$max)) $val=true;
			}
			return $val;
		}	

		function terbilang_get_str($i,$str,$len){
			$numA=array("","Satu","Dua","Tiga","Empat","Lima","Enam","Tujuh","Delapan","Sembilan");
			$numB=array("","Se","Dua ","Tiga ","Empat ","Lima ","Enam ","Tujuh ","Delapan ","Sembilan ");
			$numC=array("","Satu ","Dua ","Tiga ","Empat ","Lima ","Enam ","Tujuh ","Delapan ","Sembilan ");
			$numD=array(0=>"Puluh",1=>"Belas",2=>"Ratus",4=>"Ribu", 7=>"Juta", 10=>"Milyar", 13=>"Triliun");
			$buf="";
			$pos=$len-$i;
			switch($pos){
				case 1:
						if (!$this->terbilang_get_valid($str,$i-1,$i,1,1))
							$buf=$numA[(int) $str{$i}];
					break;
				case 2:	case 5: case 8: case 11: case 14:
						if ((int) $str{$i}==1){
							if ((int) $str{$i+1}==0)
								$buf=($numB[(int) $str{$i}]).($numD[0]);
							else
								$buf=($numB[(int) $str{$i+1}]).($numD[1]);
						}
						else if ((int) $str{$i}>1){
								$buf=($numB[(int) $str{$i}]).($numD[0]);
						}				
					break;
				case 3: case 6: case 9: case 12: case 15:
						if ((int) $str{$i}>0){
								$buf=($numB[(int) $str{$i}]).($numD[2]);
						}
					break;
				case 4: case 7: case 10: case 13:
						if ($this->terbilang_get_valid($str,$i-2,$i)){
							if (!$this->terbilang_get_valid($str,$i-1,$i,1,1))
								$buf=$numC[(int) $str{$i}].($numD[$pos]);
							else
								$buf=$numD[$pos];
						}						else if((int) $str{$i}>0){
							if ($pos==4)
								$buf=($numB[(int) $str{$i}]).($numD[$pos]);
							else
								$buf=($numC[(int) $str{$i}]).($numD[$pos]);
						}
					break;
			}
			return $buf;
		}
		function toTerbilang($nominal){
			$buf="";
			$str=$nominal."";
			$len=strlen($str);
			
			for ($i=0;$i<$len;$i++){
				$buf=trim($buf)." ".$this->terbilang_get_str($i,$str,$len);
			}
			return trim($buf);
		}
}
?>